# 计算同比并更新
# 财报显示业绩差的多久才能到稳定价格
import json
import math

import pandas as pd
from sqlalchemy import text

from tools import tool
from tools.dbTool import dbTool
from run.job.dataJob2 import getAllCode


# end_date日期结尾: 0331, 0630, 0930, 1231, 完整日期: 20190630

def calcYoyAndUpdate(code):
    sql = text(
        f"select ts_code,end_date,n_income_attr_p from ts_income where ts_code='{code}' order by end_date asc")
    conn = dbTool.getDbConn()
    df = pd.read_sql(sql, conn)
    records = df.to_dict(orient='records')
    if len(records) == 0:
        return
    # for record in records:
    records2 = {}
    for record in records:
        records2[record['end_date']] = record

    # 计算同比
    for key, record in records2.items():
        preKey = key - 10000
        if preKey not in records2:
            record['yoy'] = 0
            continue
        preRecord = records2[preKey]
        if preRecord['n_income_attr_p'] == 0:
            record['yoy'] = 0
            continue
        record['yoy'] = round((record['n_income_attr_p'] - preRecord['n_income_attr_p']) / preRecord['n_income_attr_p'],
                              4)
        if math.isnan(record['yoy']):
            record['yoy'] = 0

    # 将结果更新到数据库
    records3 = []
    for key, record in records2.items():
        records3.append(record)

    # 构造 SQL 语句和执行
    insert_statement = text(f"""
                update ts_income set yoy=:yoy where end_date=:end_date and ts_code=:ts_code
            """)
    res = conn.execute(insert_statement, records3)
    conn.commit()


def statPriceChange():
    records = dbTool.getPdList(
        f"select ts_code,end_date,n_income_attr_p,f_ann_date,yoy from ts_income where yoy < 0")
    # res = []
    # reIncomes = []
    saveRecords = []
    pos = 53805
    i = 0
    for record in records:
        i += 1
        if i < pos:
            continue
        print(f"{i}/{len(records)}")
        # print(f"code: {record['ts_code']}, end_date: {record['end_date']}")
        annDate = record['f_ann_date']
        afterDates = {}
        for offset in ['1', '7', '30', '90']:
            afterDates[offset] = str(tool.dateAdd(annDate, int(offset)))
        afterDatesStr = ','.join(list(afterDates.values()))
        records2 = dbTool.getPdList(
            f"""select ts_code,trade_date,close from ts_daily_hfq where ts_code='{record['ts_code']}' 
            and trade_date in ({annDate},{afterDatesStr}) order by trade_date asc""")
        records3 = {}
        for record2 in records2:
            records3[str(record2['trade_date'])] = record2
        if annDate not in records3:
            continue  # 公告日不是交易日跳过
        basePrice = records3[annDate]['close']
        if basePrice is None:
            continue
        for offset in afterDates.keys():
            if afterDates[offset] in records3:
                record['downPercent_' + offset] = round((records3[afterDates[offset]]['close']
                                                         - basePrice) / basePrice, 2)
            else:
                record['downPercent_' + offset] = 0
        saveRecords.append(record)
        if i % 100 == 0:
            dbTool.sqlAlchemySaveAll('rs_income', saveRecords)
            saveRecords = []


def runCalcYoyAndUpdate():
    codes = getAllCode()
    continueCode = '002865.SZ'  # 继续执行
    for code in codes:
        if continueCode == '' or code == continueCode:
            continueCode = ''
            print('code:' + code)
            calcYoyAndUpdate(code)


if __name__ == '__main__':
    # runCalcYoyAndUpdate()
    statPriceChange()
    print("over")
